﻿CREATE PROCEDURE [util].[GetDeviceDate]
@DeviceName [sysname], @DeviceDate DATETIME OUTPUT, @OutputDebugInfo BIT=0
AS
BEGIN
	DECLARE @RC int, @ERROR int
	SET @RC = 0

	DECLARE @RestoreSql nvarchar(256)
	SET @RestoreSql = N'RESTORE LABELONLY FROM [' + @DeviceName + '] WITH NOUNLOAD'
	IF(@OutputDebugInfo = 1)
		PRINT 'Restore SQL: ' + @RestoreSql

	CREATE TABLE #RESTORE_OUTPUT (
		[MediaName]  nvarchar(128)
		,[MediaSetId] uniqueidentifier
		,[FamilyCount] int
		,[FamilySequenceNumber] int
		,[MediaFamilyId] uniqueidentifier
		,[MediaSequenceNumber] int
		,[MediaLabelPresent]  tinyint
		,[MediaDescription] nvarchar(255)
		,[SoftwareName] nvarchar(128)
		,[SoftwareVendorId] int
		,[MediaDate] datetime
	)

	INSERT #RESTORE_OUTPUT (
		[MediaName]
		,[MediaSetId]
		,[FamilyCount]
		,[FamilySequenceNumber]
		,[MediaFamilyId]
		,[MediaSequenceNumber]
		,[MediaLabelPresent]
		,[MediaDescription]
		,[SoftwareName]
		,[SoftwareVendorId]
		,[MediaDate]
	)
	EXEC(@RestoreSql)
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0)
		BEGIN
			RAISERROR('Failed to restore label for device %s',16,1,@DeviceName)
			GOTO ERROR_HANDLER
		END
	
	SELECT @DeviceDate = [MediaDate]
	FROM #RESTORE_OUTPUT

	DROP TABLE #RESTORE_OUTPUT

GOTO EXIT_STATEMENT

ERROR_HANDLER:
	IF(@RC = 0) SET @RC = 1

EXIT_STATEMENT:
	IF(@RC = 0)
		PRINT 'Device date is ' + ISNULL(CONVERT(char(23),@DeviceDate,126),'NULL')
	RETURN(@RC)	
END